package com.kool.kadmin.common.coding.sqlgen;

import com.kool.kadmin.common.coding.codegen.vo.ExcelColumnVo;
import com.kool.kadmin.common.coding.codegen.vo.TableVo;
import com.kool.kadmin.common.constant.ErrorCode;
import com.kool.kadmin.common.exception.AppException;
import com.kool.kadmin.common.util.*;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.IOException;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: luyu
 * @create: 2019/02/01 17:19
 */
@Slf4j
public class ExcelToSqlTool {

    public static void main(String[] args) {
        String excel = "./doc/数据库设计.xlsx";
        String sqlFile = "./target/sql/autoGenSql.sql";
        ExcelToSqlTool.generate(excel, sqlFile);
    }

    /**
     * sql生成
     *
     * @param filePath
     */
    public static void generate(String filePath, String toDir) {
        String templatePath = PropertyUtil.getProp("common.template_path");
        try {
            List<List<List<String>>> sheets = ExcelUtil.read(filePath, 1, 65535);
            String sql = ExcelToSqlTool.generate(sheets, templatePath, toDir);
            System.out.println(sql);
        } catch (Throwable e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * excel数据库设计文档生成建表sql
     *
     * @param listSheet
     * @param templatePath
     * @param toDir
     */
    public static String generate(List<List<List<String>>> listSheet, String templatePath, String toDir) {
        StringBuffer sbSql = new StringBuffer();
        for (List<List<String>> sheet : listSheet) {
            TableVo table = sheetValueToTableBean(sheet);
            String sql = tableBeanToSql(table, templatePath);
            sbSql.append(sql);
            sbSql.append("\r\n\r\n");
        }
        if (!StringUtil.isEmpty(toDir)) {
            FileUtil.writeFile(sbSql.toString(), toDir, "UTF-8", false);
        }
        return sbSql.toString();
    }

    /**
     * @param tableBean
     * @param templatePath
     * @return
     */
    private static String tableBeanToSql(TableVo tableBean, String templatePath) {
        Configuration cfg = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        cfg.setDefaultEncoding("UTF-8");
        String sql = null;
        try {
            cfg.setDirectoryForTemplateLoading(new File(templatePath));
            Template t = cfg.getTemplate("table.ftl");
            StringWriter writer = new StringWriter();
            t.process(tableBean, writer);
            sql = writer.toString();
        } catch (IOException e) {
            throw new AppException(ErrorCode.SYSTEM_ERROR, e, StringUtil.getUUID());
        } catch (TemplateException e) {
            throw new AppException(ErrorCode.SYSTEM_ERROR, e, StringUtil.getUUID());
        }
        return sql;
    }

    /**
     * @param list
     * @return
     */
    private static TableVo sheetValueToTableBean(List<List<String>> list) {
        TableVo tableBean = new TableVo();
        String createTime = DateUtil.showTime();
        String tableId = list.get(0).get(1);
        String tableName = list.get(0).get(2);
        tableBean.setCreateTime(createTime);
        tableBean.setTableId(tableId);
        tableBean.setTableName(tableName);

        ArrayList<ExcelColumnVo> columns = new ArrayList<>();
        for (int i = 3; i < list.size() - 2; i++) {
            List<String> listCell = list.get(i);
            if (listCell.get(0) == null || listCell.get(0).equals("")) {
                continue;
            }
            String colId = listCell.get(0);
            String colName = listCell.get(1);
            String colType = listCell.get(2);
            String colComment = listCell.get(3);
            if (colComment.length() > 0) {
                colComment = "，" + colComment;
            }

            ExcelColumnVo column = new ExcelColumnVo();
            column.setId(colId);
            column.setName(colName);
            column.setType(colType);
            column.setComment(colComment.replaceAll("\n", ""));
            column.setBinary(isString(colType));
            if (isString(colType)) {
                column.setDefaultValue("''");
            } else if (isDatetime(colType)) {
                column.setDefaultValue("CURRENT_TIMESTAMP");
            } else if (isDate(colType)) {
                column.setDefaultValue("'1900-01-01'");
            } else if (isDigit(colType)) {
                column.setDefaultValue("0");
            } else {
                column.setDefaultValue("''");
            }

            columns.add(column);
        }
        tableBean.setColumns(columns);

        return tableBean;
    }

    /**
     * @param colType
     * @return
     */
    private static boolean isString(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("char") >= 0 || colType.indexOf("varchar") >= 0 || colType.indexOf("text") >= 0) {
            return true;
        }
        return false;
    }

    private static boolean isDatetime(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("datetime") >= 0 || colType.indexOf("timestamp") >= 0) {
            return true;
        }
        return false;
    }

    private static boolean isDate(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("date") >= 0) {
            return true;
        }
        return false;
    }

    private static boolean isDigit(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("int") >= 0 || colType.indexOf("bigint") >= 0) {
            return true;
        }
        return false;
    }
}
